/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.examples.enhancedads.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import com.google.api.adwords.starterkit.utils.DumpExceptions;

/**
 * Category maps the information for each category into the local
 * database.
 * 
 * @author rodrigo.f.vale@gmail.com
 */
public class Category {
  private Connection conn;
  private PreparedStatement insert;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  public Category(Connection conn) {
    this.conn = conn;
    Statement stat;
    try {
      stat = conn.createStatement();
      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS Category "
        + "  ( "
        + "     CategoryId       STRING, "          
        + "     Name             STRING, "        
        + "     LowerPrice       FLOAT, "
        + "     MaxDiscount      INTEGER, "
        + "     Items            INTEGER, "
        + "     LowerParcel      INTEGER, "
        + "     LowerParcelPrice FLOAT, "
        + "     PRIMARY KEY (CategoryId)"
        + "  ); ");

      insert = conn.prepareStatement(
          "INSERT INTO Category (CategoryId, Name, LowerPrice) " +
          "VALUES (?, ?, ?)");      
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  public void clear() {
    Statement stat;
    try {
      stat = conn.createStatement();
      stat.executeUpdate("DELETE FROM category");
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  public void saveBatch(String catId, String catName, float price) {
    try {
      insert.setString(1, catId);
      insert.setString(2, catName);
      insert.setFloat(3, price);
      insert.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  public void executeBatch() {
    try {
      insert.executeBatch();     
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  public void setLowerPrice() {
    PreparedStatement updateLowerPrice;
    log.debug("Setting LowerPrice at Ctegory");
    try {
      updateLowerPrice = conn.prepareStatement(
          "UPDATE Category SET LowerPrice = (SELECT min(Price) FROM " +
          "Catalog WHERE Catalog.CategoryId = Category.CategoryId " +
          "AND Catalog.Price > 0)");
      updateLowerPrice.executeUpdate();   
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  public void setLowerParcel() {
    log.debug("Setting LowerParcel at Category");
    PreparedStatement updateLowerParcel;
    PreparedStatement updateLowerParcelPrice;
    try {
      updateLowerParcel = conn.prepareStatement(
          "UPDATE Category SET LowerParcel = (SELECT Parcel FROM " +
          "Catalog WHERE Catalog.CategoryId = Category.CategoryId " +
          "AND Catalog.Price = Category.LowerPrice)");
      updateLowerParcel.executeUpdate();  

      updateLowerParcelPrice = conn.prepareStatement(
          "UPDATE Category SET LowerParcelPrice = (SELECT ParcelValue FROM " +
          "Catalog WHERE Catalog.CategoryId = Category.CategoryId " +
          "AND Catalog.price = Category.LowerPrice)");
      updateLowerParcelPrice.executeUpdate();   
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  public void setMaxDiscount() {
    log.debug("Setting MaxDiscount at Ctegory");
    PreparedStatement updateMaxDiscount;
    try {
      updateMaxDiscount = conn.prepareStatement(
          "UPDATE Category SET maxDiscount = (SELECT max(Discount) FROM " +
          "Catalog WHERE Catalog.CategoryId = Category.CategoryId " +
          "AND Catalog.Discount > 0)");
      updateMaxDiscount.executeUpdate();      
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  public void setItems() {
    log.debug("Setting Items at Ctegory");
    PreparedStatement updateItems;
    try {
      updateItems = conn.prepareStatement(
          "UPDATE Category SET Items = (SELECT COUNT(*) FROM " +
          "Catalog WHERE Catalog.CategoryId = Category.CategoryId)");
      updateItems.executeUpdate();    
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
}
